package com.zts.modules.sys.mapper;

import com.google.common.base.Strings;
import com.zts.modules.sys.entity.Role;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.javassist.Loader;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;
import java.util.Map;

/**
 * Created by lily on 2017/6/13.
 */
public class RoleSqlProvider {

    /**
     * 根据主键查询
     *
     * @param id
     * @return
     */
    public String get(final Long id) {
        return new SQL() {
            {
                SELECT("r.*");
                FROM("sys_role r");
                WHERE("id = #{id}");
            }
        }.toString();
    }

    /**
     * 根据关键信息查询
     *
     * @param entity
     * @return
     */
    public String getByEntity(final Role entity) {
        return new SQL() {
            {
                SELECT("r.*");
                FROM("sys_role r");
                if (!Strings.isNullOrEmpty(entity.getRoleName())) {
                    WHERE("role_name like '%'||#{roleName}||'%'");
                }
                if (!Strings.isNullOrEmpty(entity.getType())) {
                    WHERE("type = #{type}");
                }
            }
        }.toString();
    }

    /**
     * 条件查询
     *
     * @param entity
     * @return
     */
    public String getList(final Role entity) {
        return new SQL() {
            {
                SELECT("*");
                FROM("sys_role");
                if (null != entity.getId() && entity.getId() > 0) {
                    WHERE("r.id = #{id}");
                }
                if (!Strings.isNullOrEmpty(entity.getRoleName())) {
                    WHERE("role_name like '%'||#{roleName}||'%'");
                }
                if (!Strings.isNullOrEmpty(entity.getType())) {
                    WHERE("type = #{type}");
                }
                if (!Strings.isNullOrEmpty(entity.getStatus())) {
                    WHERE("status = #{status}");
                }
                if (null != entity.getCreateTime()) {
                    WHERE("create_time  >= #{createTime,jdbcType=DATE}");
                }
                ORDER_BY("id");
            }
        }.toString();
    }

    /**
     * 新增
     *
     * @param entity
     * @return
     */
    public String insert(final Role entity) {
        return new SQL() {
            {
                INSERT_INTO("sys_role");
                if (!Strings.isNullOrEmpty(entity.getRoleName())) {
                    VALUES("role_name", "#{roleName}");
                }
                if (!Strings.isNullOrEmpty(entity.getType())) {
                    VALUES("type", "#{type}");
                }
                if (!Strings.isNullOrEmpty(entity.getStatus())) {
                    VALUES("status", "#{status}");
                } else {
                    VALUES("status", "1");//默认有效
                }
                if (null != entity.getCreateTime()) {
                    VALUES("create_time", "#{createTime}");
                } else {
                    VALUES("create_time", "now()");
                }
            }
        }.toString();
    }

    /**
     * 更新保存
     *
     * @param entity
     * @return
     */
    public String update(final Role entity) {
        return new SQL() {
            {
                UPDATE("sys_role");
                if (!Strings.isNullOrEmpty(entity.getRoleName())) {
                    SET("role_name = #{roleName}");
                }
                if (!Strings.isNullOrEmpty(entity.getType())) {
                    SET("type = #{type}");
                }
                if (!Strings.isNullOrEmpty(entity.getStatus())) {
                    SET("status = #{status}");
                }
                WHERE("id = #{id}");
            }
        }.toString();
    }

    /**
     * 根据主键删除
     *
     * @param id
     * @return
     */
    public String delete(final Long id) {
        return new SQL() {
            {
                UPDATE("sys_role");
                SET("status = '0'");
                WHERE("id = #{id}");
            }
        }.toString();
    }

    /**
     * 查询当前角色是否正在使用
     *
     * @param id
     * @return
     */
    public String isUserd(final Long id) {
        return new SQL() {
            {
                SELECT("case when count(g.id) > 0 then true else false end isUserd");
                FROM("sys_grant g");
                WHERE("g.role_id =#{id} and grant_type = '1'");
            }
        }.toString();
    }

    /**
     * 查询拥有当前角色的直接用户，包括两部分：
     * 1：直接给user授权方式时的用户；
     * 2：给group授权方式时该group下的直接用户，且单独撤销授权的用户除外
     *
     * @param id
     * @return
     */
    public String getUsers(final Long id) {
        StringBuffer sql = new StringBuffer();
        //grant_type 1：增加权限;0:撤销权限
        //object_type  被授权对象类型，1：部门，2：用户
        //object_id  被授权对象的标识
        //主查询部分*****
        sql.append("select * from sys_user u where exists ( select '1' from (");
        //下面取的是直接给user授权方式时的用户
        sql.append("select object_id userId from sys_grant where role_id = #{id} and object_type = '2' and grant_type = '1'");
        sql.append(" union");
        sql.append(" select ugr.user_id userId from sys_user_group ugr");
        //给group授权方式时该group下的直接用户
        sql.append(" where exists(select '1' from sys_grant g where g.role_id = #{id} and g.object_type = '1' and grant_type = '1' and g.object_id = ugr.group_id )");
        //单独撤销授权的用户除外
        sql.append(" and not exists(select '1' from sys_grant g where g.role_id =#{id} and g.object_type = '2' and g.grant_type = '0' and g.object_id = ugr.user_id)");
        //主查询部分*****
        sql.append(") uid where uid.userId = u.id)");
        return sql.toString();
    }

    public String getAllUsers(final Long id) {
        StringBuffer sql = new StringBuffer();
        //grant_type 1：增加权限;0:撤销权限
        //object_type  被授权对象类型，1：部门，2：用户
        //object_id  被授权对象的标识
        //主查询部分*****
        sql.append("select * from sys_user u where exists ( select '1' from (");
        //下面取的是直接给user授权方式时的用户
        sql.append("select object_id userId from sys_grant where role_id = #{id} and object_type = '2' and grant_type = '1'");
        sql.append(" union");
        //给group授权方式时该group下的所有用户，包括group的递归下级组下的所有用户
        sql.append(" select ugr.user_id userId from sys_user_group ugr");
        sql.append("  where exists(");
        sql.append("         select '1' from (");
        //递归获取group的所有下级组 begin
        sql.append("               with RECURSIVE a AS(");
        sql.append("               select p.id from sys_group p where exists (select '1' from sys_grant g where g.role_id = #{id} and g.object_type = '1' and grant_type = '1' and g.object_id = p.id)");
        sql.append("               union");
        sql.append("               select p1.id from sys_group p1,a where p1.parentid = a.id");
        sql.append("               )");
        sql.append("               select * from a");
        //递归获取group的所有下级组 end
        sql.append("       ) mid where mid.id = ugr.group_id");
        sql.append("  )");
        //单独撤销授权的用户除外
        sql.append(" and not exists(select '1' from sys_grant g where g.role_id =#{id} and g.object_type = '2' and g.grant_type = '0' and g.object_id = ugr.user_id)");
        //主查询部分*****
        sql.append(") uid where uid.userId = u.id)");
        return sql.toString();
    }

    /**
     * 删除用户的grant_type=撤销权限  的记录
     * 一般用于 将用户从组中删除时
     *
     * @param param
     * @return
     */
    public String deleteRoleByUserAndGroup(final Map<String, Object> param) {
        return new SQL() {
            {
                DELETE_FROM("sys_grant g1");
                WHERE("object_id= #{userId} and object_type = '2' and grant_type = '0'");
                WHERE("role_id in (select role_id from sys_grant g2 where g2.object_id = #{groupId} and g2.object_type ='1' and g2.grant_type <> '0')");
            }
        }.toString();
    }

    /**
     * 批量删除多个用户的grant_type=撤销权限  的记录
     * 一般用于 将多个用户批量从组中删除时
     *
     * @param param
     * @return
     */
    public String deleteRoleByUsersAndGroup(final Map<String, Object> param) {
        //组装userId的in语句
        List<Long> userIds = (List<Long>) param.get("userIds");
        String inStr = "";
        if (null != userIds && userIds.size() > 0) {
            for (int i = 0; i < userIds.size(); i++) {
                inStr = inStr + userIds.get(i) + ",";
            }
            inStr = inStr.substring(0, inStr.length() - 1);
        }
        StringBuffer sql = new StringBuffer("");
        sql.append("delete from sys_grant g1 where 1=1 ");
        if (Strings.isNullOrEmpty(inStr)) {
            sql.append(" and g1.object_id is null and object_type = '2' and grant_type = '0' ");
        } else {
            sql.append(" and g1.object_id in (" + inStr + ") and object_type = '2' and grant_type = '0' ");
        }
        sql.append(" and role_id in (select role_id from sys_grant g2 where g2.object_id =" + param.get("groupId") + " and g2.object_type ='1' and g2.grant_type <> '0') ");
        return sql.toString();
    }

    /**
     * 校验授权是否存在
     *
     * @param param
     * @return
     */
    public String checkGrantExist(final Map<String, java.lang.Object> param) {
        return new SQL() {
            {
                SELECT("case when count(1) >0 then true else false end ifExist ");
                FROM("sys_grant");
                WHERE("object_id = #{objectId} and object_type = #{objectType} and role_id = #{roleId} and grant_type = #{grantType}");
            }
        }.toString();
    }

    /**
     * 用户或组单一授权
     *
     * @param param
     * @return
     */
    public String singleGrant(final Map<String, java.lang.Object> param) {
        return new SQL() {
            {
                INSERT_INTO("sys_grant");
                VALUES("object_id", "#{objectId}");
                VALUES("object_type", "#{objectType}");
                VALUES("role_id", "#{roleId}");
                VALUES("grant_type", "#{grantType}");//1：增加权限;0:撤销权限
            }
        }.toString();
    }

    /**
     * 用户或组批量授权
     *
     * @param param
     * @return
     */
    public String batchGrant(final Map<String, java.lang.Object> param) {
        //values语句
        StringBuffer sql = new StringBuffer("");
        //执行sql
        String sqlStr = "";
        sql.append("insert into sys_grant (role_id,object_id,object_type,grant_type) values ");
        //遍历组装values语句
        List<Long> roleIds = (List<Long>) param.get("roleIds");
        if (null != roleIds && roleIds.size() > 0) {
            for (int i = 0; i < roleIds.size(); i++) {
                sql.append("(").append(roleIds.get(i)).append(",");//role_id
                sql.append(param.get("objectId")).append(",");//object_id
                sql.append("'").append(param.get("objectType")).append("',");//object_type
                sql.append("'").append(param.get("grantType")).append("'").append("),");//grant_type 1：增加权限;0:撤销权限
            }
            sqlStr = sql.toString();
        }
        return sqlStr.substring(0, sqlStr.length() - 1);
    }

    /**
     * 用户或组删除单一角色
     *
     * @param param
     * @return
     */
    public String singleGrantDelete(final Map<String, java.lang.Object> param) {
        return new SQL() {
            {
                DELETE_FROM("sys_grant");
                WHERE("object_id = #{objectId}");
                WHERE("object_type = #{objectType}");
                WHERE("role_id = #{roleId}");
                WHERE("grant_type = #{grantType}");//1：增加权限;0:撤销权限
            }
        }.toString();
    }

    /**
     * 用户或组批量删除多个角色
     *
     * @param param
     * @return
     */
    public String batchGrantDelete(final Map<String, java.lang.Object> param) {

        //执行sql
        StringBuffer sql = new StringBuffer("");
        sql.append("delete from sys_grant where ");
        sql.append(" object_id = ").append(param.get("objectId"));
        sql.append(" and object_type = '").append(param.get("objectType")).append("'");
        sql.append(" and grant_type = '").append(param.get("grantType")).append("'");

        //遍历组装values语句
        List<Long> roleIds = (List<Long>) param.get("roleIds");
        String ids = StringUtils.join(roleIds.toArray(), ',');
        if (Strings.isNullOrEmpty(ids)) {
            sql.append(" and role_id is null");
        } else {
            sql.append(" and role_id in ( ").append(ids).append(" )");
        }
        return sql.toString();
    }

    /**
     * 给多个用户或组单个授权
     *
     * @param param
     * @return
     */
    public String batchGrantForObjs(final Map<String, java.lang.Object> param) {
        //values语句
        StringBuffer sql = new StringBuffer("");
        //执行sql
        String sqlStr = "";
        sql.append("insert into sys_grant (role_id,object_id,object_type,grant_type) values ");
        //遍历组装values语句
        List<Long> objectIds = (List<Long>) param.get("objectIds");
        if (null != objectIds && objectIds.size() > 0) {
            for (int i = 0; i < objectIds.size(); i++) {
                sql.append("(").append(param.get("roleId")).append(",");//role_id
                sql.append(objectIds.get(i)).append(",");//object_id
                sql.append("'").append(param.get("objectType")).append("',");//object_type
                sql.append("'").append(param.get("grantType")).append("'").append("),");//grant_type 1：增加权限;0:撤销权限
            }
            sqlStr = sql.toString();
        }
        return sqlStr.substring(0, sqlStr.length() - 1);
    }

    /**
     * 获取部门拥有的角色
     *
     * @param groupIds
     * @return
     */
    public String getGroupRoles(String groupIds) {
        //values语句
        StringBuffer sql = new StringBuffer("");

        sql.append(" WITH RECURSIVE r AS ");
        sql.append("  (select * from sys_group where  id in (  " + groupIds + " )");
        sql.append("  union  ");
        sql.append("    select g.* from sys_group g join r on g.id = r.parentid)  ");
        sql.append("  select DISTINCT a.*  ");
        sql.append("  from sys_role a  ");
        sql.append("  join sys_grant b on b.role_id = a.id  ");
        sql.append("  join r on r.id = b.object_id ");
        sql.append("  where b.grant_type = '1' and a.status = '1' ");
        return sql.toString();
    }

    /**
     * 查看用户直接拥有的角色
     *
     * @param userId
     * @return
     */
    public String getRolesByUserId(final Long userId) {
        return new SQL() {
            {
                SELECT("r.*");
                FROM("sys_role r");
                WHERE("exists (select '1' from sys_grant g where g.object_id = #{userId} and g.object_type = '2' and g.grant_type = '1' and r.id = g.role_id)");
                //去掉 排除在外即撤销权限的 记录
                WHERE("not exists (select '1' from sys_grant g where g.object_id = #{userId} and g.object_type = '2' and g.grant_type = '0' and r.id = g.role_id )");
            }
        }.toString();
    }

    public String getAllRolesByUserId(final Long userId) {
        StringBuffer sql = new StringBuffer("");
        sql.append("select * from sys_role r where id in( ");
        //查询授权表中groupId in(groupId,groupId的上级所有组的id)的记录
        sql.append("   select distinct role_id from sys_grant gr where gr.object_id in ( ");
        sql.append("          select m1.id from ( ");
        //递归查询groupId的所有上级group
        sql.append("              with recursive r as( ");
        sql.append("              select * from sys_group g where g.id in (select group_id from sys_user_group where user_id = ").append(userId).append(") ");
        sql.append("              union ");
        sql.append("              select g.* from sys_group g,r where r.parentid = g.id");
        sql.append("              ) ");
        sql.append("              select * from r ");
        sql.append("         ) m1 ");
        sql.append("   ) and gr.object_type = '1' and gr.grant_type ='1' ");
        sql.append("   union ");
        //用户直接拥有的角色
        sql.append("   (select role_id from sys_grant where object_id = ").append(userId).append("  and object_type = '2' and grant_type = '1' )");
        sql.append(") ");
        //去掉 排除在外即撤销权限的 记录
        sql.append("and not exists (select '1' from sys_grant g where g.object_id = ").append(userId).append("  and g.object_type = '2' and g.grant_type = '0' and r.id = g.role_id )");
        return sql.toString();
    }

    /**
     * 获取人拥有的角色
     *
     * @param userId
     * @return
     */
    public String getUserRoles(long userId) {
        //values语句
        StringBuffer sql = new StringBuffer("");

        sql.append("  select DISTINCT r.* from sys_role r  ");
        sql.append("  join  sys_grant g on g.role_id = r.id   ");
        sql.append("  where r.status = '1' and g.grant_type = '1' ");
        if (userId > 0) {
            sql.append(" and g.object_id = " + userId);
        }
        return sql.toString();
    }

    /**
     * 查看组直接拥有的角色
     *
     * @param groupId
     * @return
     */
    public String getRolesByGroupId(final Long groupId) {
        return new SQL() {
            {
                SELECT("r.*");
                FROM("sys_role r");
                WHERE("exists (select '1' from sys_grant g where g.object_id = #{groupId} and g.object_type = '1' and g.grant_type = '1' and r.id = g.role_id)");
                //去掉 排除在外即撤销权限的 记录
                WHERE("not exists (select '1' from sys_grant g where g.object_id = #{groupId} and g.object_type = '1' and g.grant_type = '0' and r.id = g.role_id )");
            }
        }.toString();
    }

    /**
     * 查看组拥有的所有角色，包括组（group)的递归上级组拥有的角色
     *
     * @param groupId
     * @return
     */
    public String getAllRolesByGroupId(final Long groupId) {
        StringBuffer sql = new StringBuffer("");
        sql.append("select * from sys_role  where id in( ");
        //查询授权表中groupId in(groupId,groupId的上级所有组的id)的记录
        sql.append("   select distinct role_id from sys_grant gr where gr.object_id in ( ");
        sql.append("          select m1.id from ( ");
        //递归查询groupId的所有上级group
        sql.append("              with recursive r as( ");
        sql.append("              select * from sys_group g where g.id = ").append(groupId);
        sql.append("              union ");
        sql.append("              select g.* from sys_group g,r where r.parentid = g.id");
        sql.append("              ) ");
        sql.append("              select * from r ");
        sql.append("         ) m1 ");
        sql.append("   ) and gr.object_type = '1' and gr.grant_type ='1' ");
        sql.append(")");
        return sql.toString();
    }
}
